library(XLConnect)

    
load("C:/INSOFE Material/Lab - Today/.RData")
rm(list=(ls(all=TRUE)))
setwd("C:/INSOFE Material/Life-time-value - Copy/data")
Sys.setenv(JAVA_HOME='C:\\Program Files\\Java\\jdk1.8.0_11')
Sys.setenv(JAVA_HOME= 'C:\\Program Files\\Java\\jre8')
load("C:/INSOFE Material/Life-time-value - Copy/data/.RData")

# Read in Customer Owner dataset and choose all records of date 25th Nov and Later
owner <- read.csv("stg_bgdt_cust_ownr.csv", header= TRUE, sep=",")
owner$X_NOMIN_DT <- as.Date(owner$X_NOMIN_DT, "%d/%m/%Y")
owner <- subset(owner[which(owner$X_NOMIN_DT > "2011-12-25"),])

child <- read.csv("stg_bgdt_cust_chld.csv", header = TRUE , sep=",")

# Joined all records that have common CONTACT_WID
owner_child <- merge(owner, child, by.x="CONTACT_WID", by.y = "CONTACT_WID", all=FALSE)


# Creating dummies for Child Male and Female children in Children Dataset
library(dummies)
chk_acct <- dummy(owner_child$SEX_MF_CODE,sep='_', )
owner_child <- cbind (owner_child,chk_acct)
summary(owner_child)

active <- read.csv("stg_bgdt_cust_gam_actv.csv", header = TRUE , sep=",")
active$TITLE_NOMIN_DT <- as.Date(active$TITLE_NOMIN_DT,"%d-%b-%y")
active <- subset(active[which(active$TITLE_NOMIN_DT > "2011-12-25"),])
active = active[,-7]
summary(active)


# Merging three Datasets, namely Owner, Child, Game_Active
active <- merge(owner_child, active, all=FALSE)
summary(active)
# Written owner_child_game_active_not_sum.csv - not summed on binning data wise
#write.csv(active_own, "owner_child_game_active_not_sum.csv", row.names=FALSE)


# Ready summed data with three datasets, Owner, Child, Game_Active
# 193445 records, with 30 variables
active <- read.csv("owner_child_game_active_not_sum.csv", header= TRUE, sep=",")
names(active)

# Remove the unnecessary columns....
active_columns_rem <- subset(active,select=-c(DEVICE_SRL_NUM, ASSET_WID , 
                                              X_EDW_INTEGRATION_ID, 
                                              CHILD_FIRST_NAME, 
                                              CHILD_BIRTH_DATE, 
                                              PR_HOUSEHOLD_WID,
                                              X_CRM_CUST_KEY,
                                              SEX_MF_CODE,
                                              X_TYPE,                                                                                  
                                              CHILD_CREATION_DATE,
                                              X_GRADE,X, X.1,
                                              CHILD_ROW_WID,
                                              SEX_MF_CODE_, X_PLTFRM_NM, 
                                              X_EDW_PRODUCT_NUMBER,
                                              X_CHILD_WID
                                               ))



#Binning and totalling by Date
cust_ownr7 <- subset(active_columns_rem[which(active_columns_rem$TITLE_NOMIN_DT < "2012-01-02"),])
cust_ownr30 <- subset(active_columns_rem[which(active_columns_rem$TITLE_NOMIN_DT < "2012-01-26"),])
cust_ownr90 <- subset(active_columns_rem[which(active_columns_rem$TITLE_NOMIN_DT < "2012-03-26"),])
cust_ownr180 <- subset(active_columns_rem[which(active_columns_rem$TITLE_NOMIN_DT < "2012-06-23"),])
cust_ownr360 <- subset(active_columns_rem[which(active_columns_rem$TITLE_NOMIN_DT < "2012-12-26"),])

library(plyr)
cust_ownr7_atmp_cnt <- aggregate(cust_ownr7$ATMP_CNT,by=list(CONTACT_WID =cust_ownr7$CONTACT_WID),FUN=sum)
cust_ownr30_atmp_cnt <- aggregate(cust_ownr30$ATMP_CNT,by=list(CONTACT_WID =cust_ownr30$CONTACT_WID),FUN=sum)
cust_ownr90_atmp_cnt <- aggregate(cust_ownr90$ATMP_CNT,by=list(CONTACT_WID =cust_ownr90$CONTACT_WID),FUN=sum)
cust_ownr180_atmp_cnt <- aggregate(cust_ownr180$ATMP_CNT,by=list(CONTACT_WID =cust_ownr180$CONTACT_WID),FUN=sum)
cust_ownr360_atmp_cnt <- aggregate(cust_ownr360$ATMP_CNT,by=list(CONTACT_WID =cust_ownr360$CONTACT_WID),FUN=sum)


active_time_spent <- aggregate(active_columns_rem$ACT_TME_SPN_QTY, 
                        by=list(CONTACT_WID =active_columns_rem$CONTACT_WID), 
                                 FUN=sum)

names(active_time_spent) [2] <- "TotalTimeGamePlay"
#write.csv(active_time_spent, "Total_Time_Game_Play.csv", row.names=FALSE)

active_atmps <- aggregate(active_columns_rem$ATMP_CNT ,
                          by=list(CONTACT_WID =active_columns_rem$CONTACT_WID), 
                          FUN=sum, na.rm=TRUE)
names(active_atmps) [2] <- "FreqGamePlay"
#write.csv(active_atmps, "FreqGamePlay.csv", row.names=FALSE)


# Funtion to COUNT, Individual rows, Grouped by CONTACT_WID

count <- function(NumGamesPlayed) { 
  length(na.omit(NumGamesPlayed)) 
} 

active_games_played <- aggregate(active_columns_rem$X_GAME_NM, 
                          by=list(CONTACT_WID =active_columns_rem$CONTACT_WID), 
                          FUN="count")
names(active_games_played) [2] <- "NumGamesPlayed"
#write.csv(active_games_played, "NumGamesPlayed.csv", row.names=FALSE)

count <- function(NumHouseChildren) { 
  length(na.omit(NumHouseChildren)) 
} 

active_children_house <- aggregate(active_columns_rem$CRM_CHLD_KEY, 
                                 by=list(CONTACT_WID =active_columns_rem$CONTACT_WID), 
                                 FUN="count")
names(active_children_house) [2] <- "NumHouseChildren"
#write.csv(active_children_house, "NumHouseChildren.csv", row.names=FALSE)

active_all <- merge(active_columns_rem, active_atmps,by.x="CONTACT_WID", by.y = "CONTACT_WID"
                    , all=FALSE)

active_all <- merge(active_all, active_games_played,by.x="CONTACT_WID", by.y = "CONTACT_WID"
                    , all=FALSE)

active_all <- merge(active_all, active_time_spent,by.x="CONTACT_WID", by.y = "CONTACT_WID"
                    , all=FALSE)

active_all <- merge(active_all, active_children_house,by.x="CONTACT_WID", by.y = "CONTACT_WID"
                    , all=FALSE)


#write.csv(active_all, "active_without_bin.csv", row.names=FALSE)

active_all$TITLE_NOMIN_DT <- as.Date(active_all$TITLE_NOMIN_DT)
active_all$X_NOMIN_DT <- as.Date(active_all$X_NOMIN_DT)

# Binning Data, 
# Assuming 25th December'2011 as first day of the data
# 7 day binning will show as 1, this is reflected on bin_7
# 30 day binning is reflected in bin variable
# Bin = 1 is a 30 day bin, If bin =2, it's 60 day and so on

active_all$ydaynum<-yday(active_all$TITLE_NOMIN_DT) 
active_all$yearnum<-year(active_all$TITLE_NOMIN_DT)
active_all$yearnum<-active_all$yearnum-min(active_all$yearnum)
active_all$ydaynum<-active_all$ydaynum+(active_all$yearnum*365)
active_all$ydaynum<-active_all$ydaynum-yday(min(active_all$TITLE_NOMIN_DT))
active_all$ydaynum <- active_all$ydaynum + 1

active_all$bin<-ceiling(active_all$ydaynum/30)
active_all$bin_7<-ceiling(active_all$ydaynum/7)
summary(active_all)

library(lubridate)
library(sqldf)


try_data <- sqldf ('
              SELECT  
              CONTACT_WID ,
              X_NOMIN_DT,
              MAX(TITLE_NOMIN_DT),
              MIN(TITLE_NOMIN_DT),
              MAX(TITLE_NOMIN_DT) - MIN(TITLE_NOMIN_DT) TenureDays,
              SUM(SEX_MF_CODE_M) NumMaleChildrenHousehold,
              SUM(SEX_MF_CODE_F) NumFeMaleChildrenHousehold,
              NumHouseChildren,               
              MAX(AGE) MaxChildAge,
              MIN(AGE) MinChildAge,
              MAX(AGE) - MIN(AGE) ChildAgeRange,
              X_CNTRY Country,
              NumGamesPlayed,
              CASE WHEN (BIN_7 = "1") THEN count(X_GAME_NM)
              ELSE 0              
              END NumGamesPlayed7,
              CASE WHEN (BIN = "1") THEN count(X_GAME_NM)
              ELSE 0              
              END NumGamesPlayed30,
              CASE WHEN (BIN = "1") THEN count(X_GAME_NM)
                   WHEN (BIN = "2") THEN count(X_GAME_NM)
                   WHEN (BIN = "3") THEN count(X_GAME_NM)
              ELSE 0
              END NumGamesPlayed90,
              CASE WHEN (BIN = "1") THEN count(X_GAME_NM)
                   WHEN (BIN = "2") THEN count(X_GAME_NM)
                   WHEN (BIN = "3") THEN count(X_GAME_NM)
                   WHEN (BIN = "4") THEN count(X_GAME_NM)
                   WHEN (BIN = "5") THEN count(X_GAME_NM)
                   WHEN (BIN = "6") THEN count(X_GAME_NM)
              ELSE 0
              END NumGamesPlayed180,
              CASE WHEN (BIN = "1") THEN count(X_GAME_NM)
                   WHEN (BIN = "2") THEN count(X_GAME_NM)
                   WHEN (BIN = "3") THEN count(X_GAME_NM)
                   WHEN (BIN = "4") THEN count(X_GAME_NM)
                   WHEN (BIN = "5") THEN count(X_GAME_NM)
                   WHEN (BIN = "6") THEN count(X_GAME_NM)
                   WHEN (BIN = "7") THEN count(X_GAME_NM)
                   WHEN (BIN = "8") THEN count(X_GAME_NM)
                   WHEN (BIN = "9") THEN count(X_GAME_NM)
                   WHEN (BIN = "10") THEN count(X_GAME_NM)
                   WHEN (BIN = "11") THEN count(X_GAME_NM)
                   WHEN (BIN = "12") THEN count(X_GAME_NM)
              ELSE 0
              END NumGamesPlayed360,
              FreqGamePlay,              
              CASE WHEN (BIN_7 = "1") THEN SUM(ATMP_CNT)
              ELSE 0
              END FreqGamePlay7, 
              CASE WHEN (BIN = "1") THEN SUM(ATMP_CNT)
              ELSE 0
              END FreqGamePlay30,
              CASE WHEN (BIN = "1") THEN SUM(ATMP_CNT)
                   WHEN (BIN = "2") THEN SUM(ATMP_CNT)
                   WHEN (BIN = "3") THEN SUM(ATMP_CNT)
              ELSE 0
              END FreqGamePlay90,
              CASE WHEN (BIN = "1") THEN SUM(ATMP_CNT)
                   WHEN (BIN = "2") THEN SUM(ATMP_CNT)
                   WHEN (BIN = "3") THEN SUM(ATMP_CNT)
                   WHEN (BIN = "4") THEN SUM(ATMP_CNT)
                   WHEN (BIN = "5") THEN SUM(ATMP_CNT)
                   WHEN (BIN = "6") THEN SUM(ATMP_CNT)
              ELSE 0
              END FreqGamePlay180,
              CASE WHEN (BIN = "1") THEN SUM(ATMP_CNT)
                   WHEN (BIN = "2") THEN SUM(ATMP_CNT)
                   WHEN (BIN = "3") THEN SUM(ATMP_CNT)
                   WHEN (BIN = "4") THEN SUM(ATMP_CNT)
                   WHEN (BIN = "5") THEN SUM(ATMP_CNT)
                   WHEN (BIN = "6") THEN SUM(ATMP_CNT)
                   WHEN (BIN = "7") THEN SUM(ATMP_CNT)
                   WHEN (BIN = "8") THEN SUM(ATMP_CNT)
                   WHEN (BIN = "9") THEN SUM(ATMP_CNT)
                   WHEN (BIN = "10") THEN SUM(ATMP_CNT)
                   WHEN (BIN = "11") THEN SUM(ATMP_CNT)
                   WHEN (BIN = "12") THEN SUM(ATMP_CNT)
              ELSE 0
              END FreqGamePlay360,
              TotalTimeGamePlay,
              CASE WHEN (BIN_7 = "1") THEN SUM(ACT_TME_SPN_QTY)
              ELSE 0
              END TotalTimeGamePlay7,
              CASE WHEN (BIN = "1") THEN SUM(ACT_TME_SPN_QTY)
              ELSE 0
              END TotalTimeGamePlay30,
              CASE WHEN (BIN = "1") THEN SUM(ACT_TME_SPN_QTY)
                   WHEN (BIN = "2") THEN SUM(ACT_TME_SPN_QTY)
                   WHEN (BIN = "3") THEN SUM(ACT_TME_SPN_QTY)
              ELSE 0
              END TotalTimeGamePlay90,
              CASE WHEN (BIN = "1") THEN SUM(ACT_TME_SPN_QTY)
                   WHEN (BIN = "2") THEN SUM(ACT_TME_SPN_QTY)
                   WHEN (BIN = "3") THEN SUM(ACT_TME_SPN_QTY)
                   WHEN (BIN = "4") THEN SUM(ACT_TME_SPN_QTY)
                   WHEN (BIN = "5") THEN SUM(ACT_TME_SPN_QTY)
                   WHEN (BIN = "6") THEN SUM(ACT_TME_SPN_QTY)
              ELSE 0
              END TotalTimeGamePlay180,
              CASE WHEN (BIN = "1") THEN SUM(ACT_TME_SPN_QTY)
                   WHEN (BIN = "2") THEN SUM(ACT_TME_SPN_QTY)
                   WHEN (BIN = "3") THEN SUM(ACT_TME_SPN_QTY)
                   WHEN (BIN = "4") THEN SUM(ACT_TME_SPN_QTY)
                   WHEN (BIN = "5") THEN SUM(ACT_TME_SPN_QTY)
                   WHEN (BIN = "6") THEN SUM(ACT_TME_SPN_QTY)
                   WHEN (BIN = "7") THEN SUM(ACT_TME_SPN_QTY)
                   WHEN (BIN = "8") THEN SUM(ACT_TME_SPN_QTY)
                   WHEN (BIN = "9") THEN SUM(ACT_TME_SPN_QTY)
                   WHEN (BIN = "10") THEN SUM(ACT_TME_SPN_QTY)
                   WHEN (BIN = "11") THEN SUM(ACT_TME_SPN_QTY)
                   WHEN (BIN = "12") THEN SUM(ACT_TME_SPN_QTY)
              ELSE 0
              END TotalTimeGamePlay360
              from active_all
              group by CONTACT_WID 
              ---ORDER BY CONTACT_WID
              ')


# Remove the common names columns and rename the columns with actual names

active_bin <- subset(active_bin,select=-c(X_NOMIN_DT.y, 
                                            FreqGamePlay.x, 
                                            NumGamesPlayed.x, 
                                            TotalTimeGamePlay.x, 
                                            NumHouseChildren.x ))

length(unique(active_bin$CONTACT_WID))

names(active_bin)[2] <- "X_NOMIN_DT"
names(active_bin)[22] <- "NumHouseChildren"
names(active_bin)[27] <- "NumGamesPlayed"
names(active_bin)[33] <- "FreqGamPlay"
names(active_bin)[39] <- "TotalTimeGamePlay"

names(active_bin)
summary(active_bin)


# This table is needed only for one variable - Recency Down
# Recency Down = Max(NOMIN_DT) - "2013-04-11"
cust_app <- read.csv("stg_bgdt_cust_app_dwnld.csv", header = TRUE , sep=",")
cust_app$NOMIN_DT <- as.Date(cust_app$NOMIN_DT, "%d/%m/%Y")
cust_app <- subset(cust_app[which(cust_app$NOMIN_DT > "2011-12-25"),])

cust_appl <- subset(cust_app,select=-c(DEVICE_DESCR, 
                                          DEVC_SRL_NBR, 
                                          ASSET_WID, 
                                          ITEM_NUMBER,
                                          ITEM_NAME,
                                          PKG_LIC_CD,
                                          LIC_GRNT_DT
                                          ))
# Binning Data, 
# Assuming 25th December'2011 as first day of the data
# 7 day binning will show as 1, this is reflected on bin_7
# 30 day binning is reflected in bin variable
# Bin = 1 is a 30 day bin, If bin =2, it's 60 day and so on


cust_appl$ydaynum<-yday(cust_appl$NOMIN_DT) 
cust_appl$yearnum<-year(cust_appl$NOMIN_DT)
cust_appl$yearnum<-cust_appl$yearnum-min(cust_appl$yearnum)
cust_appl$ydaynum <-cust_appl$ydaynum+(cust_appl$yearnum*365)
cust_appl$ydaynum<-cust_appl$ydaynum-yday(min(cust_appl$NOMIN_DT))
cust_appl$ydaynum <- cust_appl$ydaynum + 1

cust_appl$analysis_date <- as.Date("2013-04-11")
cust_appl$analysis_date7 <- as.Date("2012-01-02")
cust_appl$analysis_date30 <- as.Date("2012-01-26")
cust_appl$analysis_date90 <- as.Date("2012-03-23")
cust_appl$analysis_date180 <- as.Date("2012-06-23")
cust_appl$analysis_date360 <- as.Date("2012-12-23")

cust_appl$bin<-ceiling(cust_appl$ydaynum/30)
cust_appl$bin_7<-ceiling(cust_appl$ydaynum/7)
summary(cust_appl)

names(cust_appl)

try_data <- sqldf ('
                   SELECT  
                   CONTACT_WID ,
                   MAX(analysis_date) - MAX(NOMIN_DT) as Recencydown
                   from cust_appl
                   group by CONTACT_WID 
                   ORDER BY CONTACT_WID
                   ')
summary(try_data)

try_data7 <- sqldf ('
                   SELECT  
                   CONTACT_WID ,
                   MAX(analysis_date7) - MAX(NOMIN_DT) as Recencydown7
                   from cust_appl
                   where bin_7 = "1"
                   group by CONTACT_WID 
                   ORDER BY CONTACT_WID
                   ')
summary(try_data7)

try_data30 <- sqldf ('
                   SELECT  
                   CONTACT_WID ,
                    MAX(analysis_date30) - MAX(NOMIN_DT) as Recencydown30
                    from cust_appl
                    where bin = "1"
                    group by CONTACT_WID 
                    ORDER BY CONTACT_WID
                    ')
summary(try_data30)

try_data90 <- sqldf ('
                   SELECT  
                     CONTACT_WID ,
                     analysis_date90 - MAX(NOMIN_DT) as Recencydown90
                     from cust_appl
                     where ( bin = "1" or
                           bin = "2" or
                           bin = "3" )
                     group by CONTACT_WID 
                     ORDER BY CONTACT_WID
                     ')

try_data180 <- sqldf ('
                   SELECT  
                     CONTACT_WID ,
                     MAX(analysis_date180) - MAX(NOMIN_DT) as Recencydown180
                     from cust_appl
                     where ( bin = "1" or
                     bin = "2" or
                     bin = "3" or
                     bin = "4" or
                     bin = "5" or
                     bin = "6" )
                     group by CONTACT_WID 
                     ORDER BY CONTACT_WID
                     ')
try_data360 <- sqldf ('
                   SELECT  
                     CONTACT_WID ,
                      MAX(analysis_date360) - MAX(NOMIN_DT) as Recencydown360
                      from cust_appl
                      where ( bin = "1" or
                      bin = "2" or
                      bin = "3" or
                      bin = "4" or
                      bin = "5" or
                      bin = "6" or
                      bin = "7" or
                      bin = "8" or
                      bin = "9" or
                      bin = "10" or
                      bin = "11" or
                      bin = "12" )
                      group by CONTACT_WID 
                      ORDER BY CONTACT_WID
                      ')

# Pending Binnning, No real data here, 
# Probably can be ignored as much as possible
binned_recency_data <- merge(try_data, try_data7 , all=TRUE)
binned_recency_data <- merge(binned_recency_data, try_data30 , all=TRUE)
binned_recency_data <- merge(binned_recency_data, try_data90 , all=TRUE)
binned_recency_data <- merge(binned_recency_data, try_data180 , all=TRUE)
binned_recency_data <- merge(binned_recency_data, try_data360 , all=TRUE)

matchingNames <- names(active_bin)[names(active_bin) %in% names(binned_recency_data)]
matchingNames

length(unique(binned_recency_data$CONTACT_WID))

active_wid_recency <- merge(active_bin, binned_recency_data, all=FALSE)

length(unique(active_wid_recency$CONTACT_WID))

# Unnecessary record Elmination
rm(active_wid_recency)
rm(try_data)
rm(try_data7)
rm(try_data30)
rm(try_data90)
rm(try_data180)
rm(try_data360)
rm(cust_app)
rm(cust_appl)
rm(active_children_house)
rm(active_games_played)
rm(active_time_spent)
rm(active_atmps)
rm(cust_ownr180)
rm(cust_ownr30)
rm(cust_ownr360)
rm(cust_ownr7)
rm(cust_ownr90)

# Read in the Purchase_Lf 
purc_lf <- read.csv("stg_bgdt_cust_purc_lf.csv", header = TRUE , sep=",")
purc_lf$TRANS_DT <- as.Date(purc_lf$TRANSACTION_DT, "%d/%m/%Y")
purc_lf <- subset(purc_lf[which(purc_lf$TRANS_DT > "2011-12-25"),])
summary(purc_lf)
names(purc_lf)

purc_lf <- subset(purc_lf,select=-c(X_CRM_CUST_KEY,TRANSACTION_DT,ITEM,ITEM_DESCR))
names(purc_lf)[2] <- "LF_UNITS"
names(purc_lf)[3] <- "LF_AMOUNT"
names(purc_lf)[4] <- "LF_TRANS_DT"

# Binning Data, 
# Assuming 25th December'2011 as first day of the data
# 7 day binning will show as 1, this is reflected on bin_7
# 30 day binning is reflected in bin variable
# Bin = 1 is a 30 day bin, If bin =2, it's 60 day and so on

purc_lf$ydaynum<-yday(purc_lf$LF_TRANS_DT) 
purc_lf$yearnum<-year(purc_lf$LF_TRANS_DT)
purc_lf$yearnum<-purc_lf$yearnum-min(purc_lf$yearnum)
purc_lf$ydaynum <-purc_lf$ydaynum+(purc_lf$yearnum*365)
purc_lf$ydaynum<-purc_lf$ydaynum-yday(min(purc_lf$LF_TRANS_DT))
purc_lf$ydaynum <- purc_lf$ydaynum + 1

purc_lf$lf_analysis_date <- as.Date("2013-04-11")
purc_lf$lf_analysis_date7 <- as.Date("2012-01-02")
purc_lf$lf_analysis_date30 <- as.Date("2012-01-26")
purc_lf$lf_analysis_date90 <- as.Date("2012-03-23")
purc_lf$lf_analysis_date180 <- as.Date("2012-06-23")
purc_lf$lf_analysis_date360 <- as.Date("2012-12-23")

purc_lf$lf_bin<-ceiling(purc_lf$ydaynum/30)
purc_lf$lf_bin_7<-ceiling(purc_lf$ydaynum/7)
summary(purc_lf)

length(unique(purc_lf$CONTACT_WID))

#active_own_lf <- merge(active_own, purc_lf_drop, by.x="CONTACT_WID", by.y = "CONTACT_WID", all=FALSE)
#active_own_lf_all <- merge(active_own, purc_lf_drop, all=FALSE)
#summary(active_own_lf)

# ...Purchase App
purc_app <- read.csv("stg_bgdt_cust_purc_app.csv", header = TRUE , sep=",")
purc_app$PURC_APP_TRANS_DT <- as.Date(purc_app$TRANSACTION_DATE, "%d/%m/%Y")
purc_app <- subset(purc_app[which(purc_app$PURC_APP_TRANS_DT > "2011-12-25"),])
names(purc_app)[11] <- "APP_UNITS"
names(purc_app)[12] <- "APP_AMOUNT_USD"
names(purc_app)[2] <- "APP_X_CRM_CUST_KEY"
names(purc_app)[5] <- "APP_ITEM_NUMBER"

purc_app <- subset(purc_app,select=-c(APP_X_CRM_CUST_KEY,
                                      TRANSACTION_DATE,CHANNEL_DESCRIPTION,ITEM_DESCRIPTION,
                                      TRANSACTION_STORE_FRONT, SOURCE_OF_PURCHASE,LIC_CD,
                                      INSTALLED_PLATFORM))

summary(purc_app)

# Binning Data, 
# Assuming 25th December'2011 as first day of the data
# 7 day binning will show as 1, this is reflected on bin_7
# 30 day binning is reflected in bin variable
# Bin = 1 is a 30 day bin, If bin =2, it's 60 day and so on

purc_app$ydaynum<-yday(purc_app$PURC_APP_TRANS_DT) 
purc_app$yearnum<-year(purc_app$PURC_APP_TRANS_DT)
purc_app$yearnum<-purc_app$yearnum-min(purc_app$yearnum)
purc_app$ydaynum <-purc_app$ydaynum+(purc_app$yearnum*365)
purc_app$ydaynum<-purc_app$ydaynum-yday(min(purc_app$PURC_APP_TRANS_DT))
purc_app$ydaynum <- purc_app$ydaynum + 1

purc_app$app_analysis_date <- as.Date("2013-04-11")
purc_app$app_analysis_date7 <- as.Date("2012-01-02")
purc_app$app_analysis_date30 <- as.Date("2012-01-26")
purc_app$app_analysis_date90 <- as.Date("2012-03-23")
purc_app$app_analysis_date180 <- as.Date("2012-06-23")
purc_app$app_analysis_date360 <- as.Date("2012-12-23")

purc_app$app_bin<-ceiling(purc_app$ydaynum/30)
purc_app$app_bin_7<-ceiling(purc_app$ydaynum/7)
summary(purc_app)

length(unique(purc_app$CONTACT_WID))


# Load this in the last, 130MB dataset...Play_Cart has 8 million records
play_cart <- read.csv("stg_bgdt_cust_gam_play_cart.csv", header = TRUE , sep=",")
play_cart$PLAY_CART_TRANS_DT <- as.Date(play_cart$TITLE_NOMIN_DT, "%d/%m/%Y")
play_cart <- subset(play_cart[which(play_cart$PLAY_CART_TRANS_DT > "2011-12-25"),])
summary(play_cart)

play_cart <- subset(play_cart,select=-c(X_PLTFRM_NM,
                                      X_GAME_NM, X_EDW_PRODUCT_NUMBER,TITLE_NOMIN_DT,
                                      ASSET_WID, TITLE_PROD_WID, X_CRM_GAME_KEY, 
                                      CART_FLG ))

# Binning Data, 
# Assuming 25th December'2011 as first day of the data
# 7 day binning will show as 1, this is reflected on bin_7
# 30 day binning is reflected in bin variable
# Bin = 1 is a 30 day bin, If bin =2, it's 60 day and so on

play_cart$ydaynum<-yday(play_cart$PLAY_CART_TRANS_DT) 
play_cart$yearnum<-year(play_cart$PLAY_CART_TRANS_DT)
play_cart$yearnum<-play_cart$yearnum-min(play_cart$yearnum)
play_cart$ydaynum <-play_cart$ydaynum+(play_cart$yearnum*365)
play_cart$ydaynum<-play_cart$ydaynum-yday(min(play_cart$PLAY_CART_TRANS_DT))
play_cart$ydaynum <- play_cart$ydaynum + 1

play_cart$cart_analysis_date <- as.Date("2013-04-11")
play_cart$cart_analysis_date7 <- as.Date("2012-01-02")
play_cart$cart_analysis_date30 <- as.Date("2012-01-26")
play_cart$cart_analysis_date90 <- as.Date("2012-03-23")
play_cart$cart_analysis_date180 <- as.Date("2012-06-23")
play_cart$cart_analysis_date360 <- as.Date("2012-12-23")

play_cart$cart_bin<-ceiling(play_cart$ydaynum/30)
play_cart$cart_bin_7<-ceiling(play_cart$ydaynum/7)
summary(play_cart)

# Search for Names which are common in two dataframes
# here it is Play cart in Purc_APP
matchingNames <- names(play_cart)[names(play_cart) %in% names(purc_app)]
matchingNames

play_cart <- subset(play_cart,select=-c(ydaynum, yearnum ))
purc_lf <- subset(purc_lf,select=-c(ydaynum, yearnum ))
purc_app <- subset(purc_app,select=-c(ydaynum, yearnum ))

join_data <- merge(play_cart, purc_app, all=FALSE)
join_data_all <- merge(join_data, purc_lf, all= FALSE)
names(join_data_all)

OverallLastTransaction <- aggregate(join_data_all$LF_TRANS_DT,
                                    by=list(CONTACT_WID =join_data_all$CONTACT_WID), 
                                    FUN=max, na.rm=TRUE)
names(OverallLastTransaction)[2] <- "OverallLastTransaction"

try_data <- sqldf ('
                   SELECT  
                      CONTACT_WID ,
                     SUM(LF_UNITS) + SUM (APP_UNITS) as Units,
                     SUM(LF_AMOUNT) + SUM(APP_AMOUNT_USD) as TotalRevenueGenerated,
                     COUNT(APP_ITEM_NUMBER) as NumGamesBought,
                     COUNT(LF_TRANS_DT) FrequencyLF,
                     COUNT(PURC_APP_TRANS_DT) FrequencyApp
                     from join_data_all
                     group by CONTACT_WID 
                     ORDER BY CONTACT_WID
                     ')



try_data7 <- sqldf ('
                   SELECT  
                     CONTACT_WID ,
                     SUM(LF_UNITS) + SUM (APP_UNITS) as Units7,
                     SUM(LF_AMOUNT) + SUM(APP_AMOUNT_USD) as Revenue7,
                     COUNT(LF_TRANS_DT) FrequencyLF7,
                     COUNT(PURC_APP_TRANS_DT) FrequencyApp7
                     from join_data_all
                     where ( lf_bin_7 = "1" or
                     app_bin_7 = "1" )
                     group by CONTACT_WID 
                     ORDER BY CONTACT_WID
                     ')

try_data30 <- sqldf ('
                   SELECT  
                      CONTACT_WID ,
                      SUM(LF_UNITS) + SUM (APP_UNITS) as Units30,
                      SUM(LF_AMOUNT) + SUM(APP_AMOUNT_USD) as Revenue30,
                      COUNT(LF_TRANS_DT) FrequencyLF30,
                      COUNT(PURC_APP_TRANS_DT) FrequencyApp30
                      from join_data_all
                      where ( lf_bin = "1" or
                      app_bin = "1" )
                      group by CONTACT_WID 
                      ORDER BY CONTACT_WID
                      ')

try_data90 <- sqldf ('
                     SELECT  
                     CONTACT_WID ,
                     SUM(LF_UNITS) + SUM (APP_UNITS)  as Units90,
                     SUM(LF_AMOUNT) + SUM(APP_AMOUNT_USD) as Revenue90,
                      COUNT(LF_TRANS_DT) FrequencyLF90,
                      COUNT(PURC_APP_TRANS_DT) FrequencyApp90
                     from join_data_all
                     where 
                      ( 
                        lf_bin = "1" or
                        app_bin = "1" or
                        lf_bin = "2" or
                        app_bin = "2" or
                        lf_bin = "3" or
                        app_bin = "3"
                      )
                     group by CONTACT_WID 
                     ORDER BY CONTACT_WID
                     ')

try_data180 <- sqldf ('
                     SELECT  
                     CONTACT_WID ,
                     SUM(LF_UNITS) + SUM (APP_UNITS)  as Units180,
                     SUM(LF_AMOUNT) + SUM(APP_AMOUNT_USD) as Revenue180,
                      COUNT(LF_TRANS_DT) FrequencyLF180,
                      COUNT(PURC_APP_TRANS_DT) FrequencyApp180
                     from join_data_all
                     where 
                     ( 
                     lf_bin = "1" or
                     app_bin = "1" or
                     lf_bin = "2" or
                     app_bin = "2" or
                     lf_bin = "3" or
                     app_bin = "3" or
                     lf_bin = "4" or
                     app_bin = "4" or
                     lf_bin = "5" or
                     app_bin = "5" or
                     lf_bin = "6" or
                     app_bin = "6" 
                     )
                     group by CONTACT_WID 
                     ORDER BY CONTACT_WID
                     ')


try_data360 <- sqldf ('
                      SELECT  
                      CONTACT_WID ,
                      SUM(LF_UNITS) + SUM (APP_UNITS) as Units360,
                      SUM(LF_AMOUNT) + SUM(APP_AMOUNT_USD) as Revenue360,
                      COUNT(LF_TRANS_DT) FrequencyLF360,
                      COUNT(PURC_APP_TRANS_DT) FrequencyApp360
                      from join_data_all
                      where 
                      ( 
                      lf_bin = "1" or
                      app_bin = "1" or
                      lf_bin = "2" or
                      app_bin = "2" or
                      lf_bin = "3" or
                      app_bin = "3" or
                      lf_bin = "4" or
                      app_bin = "4" or
                      lf_bin = "5" or
                      app_bin = "5" or
                      lf_bin = "6" or
                      app_bin = "6" or
                      lf_bin = "7" or
                      app_bin = "7" or
                      lf_bin = "8" or
                      app_bin = "8" or
                      lf_bin = "9" or
                      app_bin = "9" or
                      lf_bin = "10" or
                      app_bin = "10" or
                      lf_bin = "11" or
                      app_bin = "11" or
                      lf_bin = "12" or
                      app_bin = "12"

                      )
                      group by CONTACT_WID 
                      ORDER BY CONTACT_WID
                      ')

revenue_data <- merge (try_data, try_data30, all=TRUE)
revenue_data <- merge (revenue_data, try_data90, all=TRUE)
revenue_data <- merge (revenue_data, try_data180, all=TRUE)
revenue_data <- merge (revenue_data, try_data360, all=TRUE)
revenue_data <- merge (revenue_data, try_data7, all=TRUE)
revenue_data <- merge (revenue_data, OverallLastTransaction, all=TRUE)


# Imputing all NA's with Zeros
revenue_data$Units7[is.na(revenue_data$Units7)] <- 0
revenue_data$Units30[is.na(revenue_data$Units30)] <- 0
revenue_data$Units90[is.na(revenue_data$Units90)] <- 0
revenue_data$Units180[is.na(revenue_data$Units180)] <- 0
revenue_data$Units360[is.na(revenue_data$Units360)] <- 0
revenue_data$Units[is.na(revenue_data$Units)] <- 0

revenue_data$TotalRevenueGenerated[is.na(revenue_data$TotalRevenueGenerated)] <- 0
revenue_data$Revenue7[is.na(revenue_data$Revenue7)] <- 0
revenue_data$Revenue30[is.na(revenue_data$Revenue30)] <- 0
revenue_data$Revenue90[is.na(revenue_data$Revenue90)] <- 0
revenue_data$Revenue180[is.na(revenue_data$Revenue180)] <- 0
revenue_data$Revenue360[is.na(revenue_data$Revenue360)] <- 0

revenue_data$FrequencyLF[is.na(revenue_data$FrequencyLF)] <- 0
revenue_data$FrequencyLF7[is.na(revenue_data$FrequencyLF7)] <- 0
revenue_data$FrequencyLF30[is.na(revenue_data$FrequencyLF30)] <- 0
revenue_data$FrequencyLF90[is.na(revenue_data$FrequencyLF90)] <- 0
revenue_data$FrequencyLF180[is.na(revenue_data$FrequencyLF180)] <- 0
revenue_data$FrequencyLF360[is.na(revenue_data$FrequencyLF360)] <- 0

revenue_data$FrequencyApp[is.na(revenue_data$FrequencyApp)] <- 0
revenue_data$FrequencyApp7[is.na(revenue_data$FrequencyApp7)] <- 0
revenue_data$FrequencyApp30[is.na(revenue_data$FrequencyApp30)] <- 0
revenue_data$FrequencyApp90[is.na(revenue_data$FrequencyApp90)] <- 0
revenue_data$FrequencyApp180[is.na(revenue_data$FrequencyApp180)] <- 0
revenue_data$FrequencyApp360[is.na(revenue_data$FrequencyApp360)] <- 0

summary(revenue_data)

# Search for Names which are common in two dataframes
# here it is Play cart in Purc_APP
matchingNames <- names(active_bin)[names(active_bin) %in% names(revenue_data)]
matchingNames

length(unique(active_bin$CONTACT_WID))
length(unique(active_wid_recency$CONTACT_WID))
length(unique(revenue_data$CONTACT_WID))
length(unique(Revenues$CONTACT_WID))

final_joins <- merge(active_bin, revenue_data, all=FALSE )
write.csv(final_joins, "Life_Time_Value.csv", row.names=FALSE)


#rm(active_all)
rm(final_joins)
rm(active_all)
rm(try_data)
rm(try_data180)
rm(try_data360)
rm(try_data90)
rm(try_data7)
rm(try_data30)
rm(join_data)
rm(join_data_all)

library(dummies)

LTV_All <- read.csv("Life_Time_Value.csv", header= TRUE, sep=",")
summary(LTV_All)
names(LTV_All)

LTV_All_bin <- subset(LTV_All,select=c(CONTACT_WID, 
                                        MAX.TITLE_NOMIN_DT.,
                                        MIN.TITLE_NOMIN_DT.,
                                        TenureDays,
                                        NumHouseChildren,
                                        NumMaleChildrenHousehold,
                                        NumFeMaleChildrenHousehold,
                                        MaxChildAge,
                                        MinChildAge,
                                        ChildAgeRange,
                                        Country,
                                        NumGamesPlayed, 
                                        NumGamesPlayed7,
                                        NumGamesPlayed30,
                                        NumGamesPlayed90,
                                        NumGamesPlayed180,
                                        NumGamesPlayed360,
                                        FreqGamPlay, 
                                        FreqGamePlay7, 
                                        FreqGamePlay30, 
                                        FreqGamePlay90, 
                                        FreqGamePlay180,
                                        FreqGamePlay360, 
                                        TotalTimeGamePlay,
                                        TotalTimeGamePlay7,
                                        TotalTimeGamePlay30,
                                        TotalTimeGamePlay90,
                                        TotalTimeGamePlay180,
                                        TotalTimeGamePlay360,
                                        Units, 
                                        Units7,
                                        Units30,
                                        Units90,
                                        Units180,
                                        Units360,
                                        # Removing Directly Revenue Related fields
                                        #Revenue7,
                                        #Revenue30,
                                        #Revenue90,
                                        #Revenue180,
                                        #Revenue360,
                                        NumGamesBought,
                                        FrequencyLF,
                                        FrequencyLF7,
                                        FrequencyLF30,
                                        FrequencyLF90,
                                        FrequencyLF180,
                                        FrequencyLF360,
                                        FrequencyApp,
                                        FrequencyApp7,
                                        FrequencyApp30,
                                        FrequencyApp90,
                                        FrequencyApp180,
                                        FrequencyApp360,
                                        TotalRevenueGenerated
                                        #OverallLastTransaction
                                       ))

# Trying to Plot Relationship between paramters
#boxplot(LTV_All$AGE)
#boxplot(LTV_All$Revenue7)
#boxplot(LTV_All$Revenue30)
#boxplot(LTV_All$Revenue90)


#Revenues <- read.csv("revenues_data.csv", header= TRUE, sep=",")
#names(Revenues)

# Find Important Components
Classify <- subset(Classify,select=c(CONTACT_WID, NumHouseChildren,
                                        NumMaleChildrenHousehold,
                                        NumFeMaleChildrenHousehold,
                                        NumGamesPlayed, 
                                        FreqGamPlay, TotalTimeGamePlay,
                                        Units, TotalRevenueGenerated,
                                        NumGamesBought,
                                        TenureDays, 
                                        OverallLastTransaction))


Classify_numeric <- subset(Classify,select=c(NumHouseChildren,
                                     NumMaleChildrenHousehold,
                                     NumFeMaleChildrenHousehold,
                                     NumGamesPlayed, 
                                     FreqGamPlay, TotalTimeGamePlay,
                                     Units, TotalRevenueGenerated,
                                     NumGamesBought, TenureDays))

#Data standardization
# Not Standardizing, as explicability in the points is missing
# Guidelines from Mentors
#library(vegan)
##Classify_numeric <- na.omit(Classify_numeric)
#std.data <- decostand(Classify_numeric,"range",na.rm=TRUE)
#head(std.data)
#summary(std.data)

# Creating training and test datasets - for Linear Regression on Entire Dataset
rows=seq(1,10810,1)
set.seed(123)
trainRows=sample(rows,6400)
set.seed(123)
remainingRows=rows[-(trainRows)]
testRows=sample(remainingRows, 4410)

train = LTV_All_bin[trainRows,] 
test=LTV_All_bin[testRows,]

library(MASS)
library(car)
library(miscTools)
library(rpart)

# Buiding Regression Model ---
attach(train)


with(train, cor(TotalRevenueGenerated, TotalTimeGamePlay))
with(train, cor(TotalRevenueGenerated, NumHouseChildren))
with(train, cor(TotalRevenueGenerated, FreqGamPlay))
# 0.96 Correlation for Units and Revenues
with(train, cor(TotalRevenueGenerated, Units))
with(train, cor(TotalRevenueGenerated, TenureDays))

# 0.98 Correlation
with(train, cor(TotalRevenueGenerated, NumGamesBought))

# 1 Correlation, This is good correlation
with(train, cor(NumGamesPlayed, NumHouseChildren))
with(train, cor(NumGamesBought, NumHouseChildren))
with(train, cor(Units, NumHouseChildren))

alias( Regression_All <- lm(TotalRevenueGenerated ~   
                               MAX.TITLE_NOMIN_DT. +
                               MIN.TITLE_NOMIN_DT. +
                               TenureDays          +
                               NumHouseChildren    + 
                               NumMaleChildrenHousehold   + 
                               NumFeMaleChildrenHousehold + 
                               MaxChildAge                +
                               MinChildAge +
                               ChildAgeRange              +
                               NumGamesPlayed + 
                               NumGamesPlayed7 +
                               NumGamesPlayed30 +
                               NumGamesPlayed90 +
                               NumGamesPlayed180 +
                               NumGamesPlayed360 +
                               FreqGamPlay + 
                               FreqGamePlay7 + 
                               FreqGamePlay30 + 
                               FreqGamePlay90 + 
                               FreqGamePlay180 +
                               FreqGamePlay360 + 
                               TotalTimeGamePlay +
                               TotalTimeGamePlay7 +
                               TotalTimeGamePlay30 +
                               TotalTimeGamePlay90 +
                               TotalTimeGamePlay180 +
                               TotalTimeGamePlay360 +
                               Units + 
                               Units7 +
                               Units30 +
                               Units90 +
                               Units180 +
                               Units360 +
                               #Revenue7 +
                               #Revenue30 +
                               #Revenue90 +
                               #Revenue180 +
                               #Revenue360 +
                               NumGamesBought +
                               FrequencyLF +
                               FrequencyLF7 +
                               FrequencyLF30 +
                               FrequencyLF90 +
                               FrequencyLF180 +
                               FrequencyLF360 +
                               FrequencyApp +
                               FrequencyApp7 +
                               FrequencyApp30 +
                               FrequencyApp90 +
                               FrequencyApp180 +
                               FrequencyApp360 
                            ) )   
   
  )

vif(Regression_All <- lm(TotalRevenueGenerated ~Units + 
                           FreqGamPlay + TotalTimeGamePlay + 
                           NumGamesBought + NumHouseChildren + TenureDays))

# GVIF Values for NumGamesBought (23.8) and Units(23.5) is Greater than 5, this means
# They are highly collinear, removing Units and NumGamesBought

Regression_All <- lm(TotalRevenueGenerated ~   
                       MAX.TITLE_NOMIN_DT. +
                       MIN.TITLE_NOMIN_DT. +
                       TenureDays          +
                       NumHouseChildren    + 
                       NumMaleChildrenHousehold   + 
                       NumFeMaleChildrenHousehold + 
                       MaxChildAge                +
                       MinChildAge +
                       ChildAgeRange              +
                       NumGamesPlayed + 
                       NumGamesPlayed7 +
                       NumGamesPlayed30 +
                       NumGamesPlayed90 +
                       NumGamesPlayed180 +
                       NumGamesPlayed360 +
                       FreqGamPlay + 
                       FreqGamePlay7 + 
                       FreqGamePlay30 + 
                       FreqGamePlay90 + 
                       FreqGamePlay180 +
                       FreqGamePlay360 + 
                       TotalTimeGamePlay +
                       TotalTimeGamePlay7 +
                       TotalTimeGamePlay30 +
                       TotalTimeGamePlay90 +
                       TotalTimeGamePlay180 +
                       TotalTimeGamePlay360 +
                       Units + 
                       Units7 +
                       Units30 +
                       Units90 +
                       Units180 +
                       Units360 +
                       ## All Revenue Related Fields are being removed
                       #Revenue7 +
                       ##Revenue30 +
                       #Revenue90 +
                       #Revenue180 +
                       #Revenue360 +
                       NumGamesBought +
                       FrequencyLF +
                       FrequencyLF7 +
                       FrequencyLF30 +
                       FrequencyLF90 +
                       FrequencyLF180 +
                       FrequencyLF360 +
                       FrequencyApp +
                       FrequencyApp7 +
                       FrequencyApp30 +
                       FrequencyApp90 +
                       FrequencyApp180 +
                       FrequencyApp360 
                        )

summary(Regression_All)

onlytest <- test
onlytest <- test[-1]
testresult <- onlytest[48]
onlytest <- onlytest[-48]


lm.Result <- as.data.frame(predict(Regression_All, onlytest))
body(Regression_All)

library(DMwR)
regr.eval(train.act[,"TotalRevenueGenerated"], Regression_All, 
          train.y = train.act[,"TotalRevenueGenerated"])


r2_train <- rSquared(train$TotalRevenueGenerated, Regression_All$residuals )

test.pred <- as.matrix(test)%*%Regression_All$coefficients
mse.train <- summary(Regression_All)$sigma^2
mse.test  <- sum((test.pred - test$TotalRevenueGenerated)^2)/(10810-length(train)-2)

r2_test <- rSquared(test$TotalRevenueGenerated, resid = test.pred-test$TotalRevenueGenerated)

#ncvTest(Regression_All)
#qqPlot(Regression_All)
#outlierTest(Regression_All)
#residualPlots(Regression_All)

predLmTest  =predict(Regression_All, newdata=test)
test.lm <- predict(Regression_All, newdata=test)
test.predict <- predict.lm(Regression_All, test, interval="confidence") 


#calc.relimp(Data_Cluster_1,type=c("lmg","last","first","pratt"),
#            rela=TRUE)

confint(Regression_All, level=0.95)
step <- stepAIC(Regression, direction="both")
vcov(Regression_All)
anova(Regression_All)
coefficients(Regression_All)

# Decision Tree
library(rpart)
fit <- rpart(TotalRevenueGenerated ~   
               MAX.TITLE_NOMIN_DT. +
               MIN.TITLE_NOMIN_DT. +
               TenureDays          +
               NumHouseChildren    + 
               NumMaleChildrenHousehold   + 
               NumFeMaleChildrenHousehold + 
               MaxChildAge                +
               MinChildAge                +
               ChildAgeRange              +
               NumGamesPlayed + 
               NumGamesPlayed7 +
               NumGamesPlayed30 +
               NumGamesPlayed90 +
               NumGamesPlayed180 +
               NumGamesPlayed360 +
               FreqGamPlay + 
               FreqGamePlay7 + 
               FreqGamePlay30 + 
               FreqGamePlay90 + 
               FreqGamePlay180 +
               FreqGamePlay360 + 
               TotalTimeGamePlay +
               TotalTimeGamePlay7 +
               TotalTimeGamePlay30 +
               TotalTimeGamePlay90 +
               TotalTimeGamePlay180 +
               TotalTimeGamePlay360 +
               Units + 
               Units7 +
               Units30 +
               Units90 +
               Units180 +
               Units360 +
               ## All Revenue Related Fields are being removed
               #Revenue7 +
               ##Revenue30 +
               #Revenue90 +
               #Revenue180 +
               #Revenue360 +
               NumGamesBought +
               FrequencyLF +
               FrequencyLF7 +
               FrequencyLF30 +
               FrequencyLF90 +
               FrequencyLF180 +
               FrequencyLF360 +
               FrequencyApp +
               FrequencyApp7 +
               FrequencyApp30 +
               FrequencyApp90 +
               FrequencyApp180 +
               FrequencyApp360,  
             data=train,method="anova"
             )

printcp(fit) # display the results 
plotcp(fit) # visualize cross-validation results 
summary(fit) # detailed summary of splits

predCartTrain =predict(fit, newdata=train, type="vector")
predCartTest  =predict(fit, newdata=test, type="vector")

plot(fit,main="Decision Tree for Revenue",
     margin=0.15,uniform=TRUE)
text(fit,use.n=T, all=TRUE, cex=.6)

  
regr.eval(train[,"TotalRevenueGenerated"], 
          predCartTrain, train.y = train[,"TotalRevenueGenerated"])
regr.eval(test[,"TotalRevenueGenerated"], 
          predCartTest, train.y = test[,"TotalRevenueGenerated"])

# Applying Random Forest to Test and Train Data

library(randomForest)
set.seed(123)
trainact <- train[-1]
trainact <- trainact[-10]

rf <- randomForest(TotalRevenueGenerated ~ ., data=trainact, 
                   scale=TRUE, keep.forest=TRUE, ntree=300)
summary(rf)


testrem <- test [-1]
testrem <- testrem[-10]

round(importance(rf), 2)
importance(rf, type=2, scale=TRUE)

summary(predTest)
names(predTest)
summary(predTrain)

#library(varSelRF)
#rf.vsl <- randomVarImpsRF(trainact,clusterfit,
##                rf,
#                numrandom = 20,
#                usingCluster = TRUE)


regr.eval(trainact[,"TotalRevenueGenerated"], 
          predTrain,
          train.y = testrem[,"TotalRevenueGenerated"])


regr.eval(testrem[,"TotalRevenueGenerated"], 
          predTest, 
          train.y = testrem[,"TotalRevenueGenerated"])

library(randomForest)
set.seed(123)
trainact <- train[-1]
trainact <- trainact[-10]

rf <- randomForest(TotalRevenueGenerated ~ ., data=train.act, 
                   keep.forest=TRUE, ntree=300)
summary(rf)


testrem <- test [-1]
testrem <- testrem[-10]

round(importance(rf), 2)
varImpPlot(rf,main="",col="dark blue")

#rf_test=table(testrem$TotalRevenueGenerated, predict(rf, testrem, 
# #                                               type="response", norm.votes=TRUE))

summary(predTest)
names(predTest)
summary(predTrain)

regr.eval(train.act[,"TotalRevenueGenerated"], 
          predTrain,
          train.y = testrem[,"TotalRevenueGenerated"])


regr.eval(testrem[,"TotalRevenueGenerated"], 
          predTest, 
          train.y = testrem[,"TotalRevenueGenerated"])


# Segmentation for data
# Use K-Means for Analysis
# K-Means Cluster Analysis
set.seed(123)
WSS <- 0
for (i in 1:15) {
  WSS[i] <- sum(kmeans(LTV_All_bin,centers=i)$withinss)
}

plot(1:15, wss, 
     type="b", 
     xlab="Number of Clusters",
     ylab="Within groups sum of squares") 

LTV_All_bin_cluster <- LTV_All_bin
summary(LTV_All_bin_cluster)
names(LTV_All_bin_cluster)
# Remove Country
LTV_All_bin_cluster <- LTV_All_bin_cluster [-11]
LTV_All_bin_cluster <- LTV_All_bin_cluster [-1]

set.seed(123)
WSS <- 0
for (i in 1:15) {
  WSS[i] <- sum(kmeans(LTV_All_bin_cluster,centers=i)$withinss)
}

plot(1:15, wss, 
     type="b", 
     xlab="Number of Clusters",
     ylab="Within groups sum of squares") 


clusterfit <- kmeans(LTV_All_bin_cluster, 3) # 3 cluster solution
# get cluster means
aggregate(LTV_All_bin_cluster,by=list(clusterfit$cluster),
          FUN=mean)

# append cluster assignment
LTV_All_bin_cluster <- data.frame(LTV_All_bin_cluster,clusterfit$cluster) 
head(LTV_All_bin_cluster)

Data_Cluster_1 <- subset(LTV_All_bin_cluster
                         [which(LTV_All_bin_cluster$clusterfit.cluster==1),])


Data_Cluster_2 <- subset(LTV_All_bin_cluster
                         [which(LTV_All_bin_cluster$clusterfit.cluster==2),])

Data_Cluster_3 <- subset(LTV_All_bin_cluster
                         [which(LTV_All_bin_cluster$clusterfit.cluster==3),])


length(which(LTV_All_bin_cluster$clusterfit.cluster==1))
length(which(LTV_All_bin_cluster$clusterfit.cluster==2))
length(which(LTV_All_bin_cluster$clusterfit.cluster==3))

write.csv(LTV_All_bin_cluster, "LTV_with_Cluster.csv", row.names=FALSE)

write.csv(Data_Cluster_3, "LTV_Cluster_3.csv", row.names=FALSE)
write.csv(Data_Cluster_2, "LTV_Cluster_2.csv", row.names=FALSE)
write.csv(Data_Cluster_1, "LTV_Cluster_1.csv", row.names=FALSE)

boxplot(Data_Cluster_3$TotalRevenueGenerated ~ 
          Data_Cluster_3$clusterfit.cluster)

summary(Data_Cluster_3)

# Explicability of the Clusters
explain_3 <- subset(Data_Cluster_3,select=c(NumHouseChildren,
                                             NumMaleChildrenHousehold,
                                             NumFeMaleChildrenHousehold,
                                             NumGamesPlayed, 
                                             FreqGamPlay, TotalTimeGamePlay,
                                             Units, TotalRevenueGenerated,
                                             NumGamesBought, TenureDays))

summary(explain_3)

explain_2 <- subset(Data_Cluster_2,select=c(NumHouseChildren,
                                            NumMaleChildrenHousehold,
                                            NumFeMaleChildrenHousehold,
                                            NumGamesPlayed, 
                                            FreqGamPlay, TotalTimeGamePlay,
                                            Units, TotalRevenueGenerated,
                                            NumGamesBought, TenureDays))

summary(explain_2)

Revenueperchild_1=
  explain_1$TotalRevenueGenerated/explain_1$NumHouseChildren
Revenueperchild_2=
  explain_2$TotalRevenueGenerated/explain_2$NumHouseChildren
Revenueperchild_3=
  explain_3$TotalRevenueGenerated/explain_3$NumHouseChildren

summary(Revenueperchild_1)
summary(Revenueperchild_2)
summary(Revenueperchild_3)

RevenueperGameB_1=
  explain_1$TotalRevenueGenerated/explain_1$NumGamesBought
RevenueperGameB_2=
  explain_2$TotalRevenueGenerated/explain_2$NumGamesBought
RevenueperGameB_3=
  explain_3$TotalRevenueGenerated/explain_3$NumGamesBought

summary(RevenueperGameB_1)
summary(RevenueperGameB_2)
summary(RevenueperGameB_3)


RevenueperGameP_1=
  explain_1$TotalRevenueGenerated/explain_1$NumGamesPlayed
RevenueperGameP_2=
  explain_2$TotalRevenueGenerated/explain_2$NumGamesPlayed
RevenueperGameP_3=
  explain_3$TotalRevenueGenerated/explain_3$NumGamesPlayed

summary(RevenueperGameP_1)
summary(RevenueperGameP_2)
summary(RevenueperGameP_3)

RevenueperFrequency_1=
  explain_1$TotalRevenueGenerated/explain_1$FreqGamPlay
RevenueperFrequency_2=
  explain_2$TotalRevenueGenerated/explain_2$FreqGamPlay
RevenueperFrequency_3=
  explain_3$TotalRevenueGenerated/explain_3$FreqGamPlay

summary(RevenueperFrequency_1)
summary(RevenueperFrequency_2)
summary(RevenueperFrequency_3)

RevenueperTenure_1=
  explain_1$TotalRevenueGenerated/explain_1$TenureDays
RevenueperTenure_2=
  explain_2$TotalRevenueGenerated/explain_2$TenureDays
RevenueperTenure_3=
  explain_3$TotalRevenueGenerated/explain_3$TenureDays

summary(RevenueperTenure_1)
summary(RevenueperTenure_2)
summary(RevenueperTenure_3)

Tie_Ratio_1=
  explain_1$Units/explain_1$NumGamesBought
Tie_Ratio_2=
  explain_2$Units/explain_2$NumGamesBought
Tie_Ratio_3=
  explain_3$Units/explain_3$NumGamesBought

summary(Tie_Ratio_1)
summary(Tie_Ratio_2)
summary(Tie_Ratio_3)

predCartTrain =predict(fit, newdata=train, type="vector")
predCartTest  =predict(fit, newdata=test, type="vector")


predTrain=predict(rf, newdata=trainact, type="response", norm.votes=TRUE)
predTest=predict(rf, newdata=testrem, type="response", norm.votes=TRUE)

Stack <- as.data.frame (predCartTrain)
Stack <- cbind(Stack,predTrain)
Stack <- cbind(Stack,trainact$TotalRevenueGenerated)
names(Stack) [1] <- "Cart"
names (Stack)[2] <- "RF"
names (Stack) [3] <- "Revenue"

Stack_test <- as.data.frame (predCartTest)
Stack_test <- cbind(Stack_test,predTest)
#Stack_test <- cbind(Stack_test,testrem$TotalRevenueGenerated)
names(Stack_test) [1] <- "testCart"
names (Stack_test)[2] <- "testRF"
#names (Stack_test) [3] <- "testRevenue"


attach(Stack)
Stack_All <- lm(Revenue ~   
                  RF +
                  Cart
                
)
detach(Stack)

test.stack <- as.matrix(testrem$TotalRevenueGenerated)%*%Stack_All$coefficients
test.stack <- as.data.frame(test.stack)
mse.train <- summary(Regression_All)$sigma^2
mse.test  <- sum((test.stack - test$TotalRevenueGenerated)^2)/(10810-length(train)-2)
